package javamusic.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;

import javamusic.excepcion.DAOExcepcion;
import javamusic.modelo.Reserva;
import javamusic.modelo.Cliente;
import javamusic.util.ConexionBDKaraoke;

import java.sql.Date;


public class ReservaDAO extends BaseDAO{


	public Collection<Reserva> BuscarReserva(int dni, Date fechaInicio, Date fechaFin) throws DAOExcepcion {
		
		//String query = "select idreserva, fecha, descripcion,idsala,b.idcliente from reserva a inner join cliente b on (a.idcliente = b.idcliente) inner join persona c on (b.dni = c.dni) where c.dni = ifnull(?,c.dni) and fecha = fecha )";
		
		/*
		 * 
		 CREATE  PROCEDURE `sp_sel_reserva`(
			in p_dni int,
			in p_fechaI Date,
			in p_fechaF Date)
			BEGIN
			
			if(p_dni =0) then 
			set p_dni = null;
			end if;
			
			if(p_fechaI = '1901-01-01') then 
			set p_fechaI = null;
			end if;
			
			
			select idreserva, fecha, descripcion,idsala,b.idcliente,c.nombres,c.paterno,c.materno,b.correo
			from reserva a inner join cliente b on (a.idcliente = b.idcliente) 
			inner join persona c on (b.dni = c.dni) 
			where c.dni = ifnull(p_dni,c.dni)
			and ( p_fechaI is null or ( Date(fecha) >= Date(p_fechaI) and Date(fecha) <= Date(p_fechaF)))
			and elog = 1
			order by fecha desc;
			
			END
		 */
		
		String query = "call sp_sel_reserva(?,?,?)";
		Collection<Reserva> lista = new ArrayList<Reserva>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, dni);
			stmt.setDate(2, fechaInicio);
			stmt.setDate(3, fechaFin);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Reserva vo = new Reserva();
				Cliente cliente = new Cliente();
				vo.setCliente(cliente);
				vo.setIdReserva(rs.getInt("idreserva"));
				vo.setFecha(rs.getDate("fecha"));
				vo.setDescripcion(rs.getString("descripcion"));
				vo.getCliente().setCorreo(rs.getString("correo"));
				vo.getCliente().setNombre(rs.getString("nombres"));
				vo.getCliente().setApePaterno(rs.getString("paterno"));
				vo.getCliente().setApeMaterno(rs.getString("materno"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	public Reserva insertar(Reserva vo) throws DAOExcepcion {
		
		String query = "insert into reserva(fecha,descripcion,elog,idsala,idcliente) values (?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		Reserva result = null;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setDate(1, vo.getFecha());
			stmt.setString(2, vo.getDescripcion());
			stmt.setInt(3, vo.getElog());
			stmt.setInt(4, vo.getSala().getIdsala());
			stmt.setInt(5, vo.getCliente().getIdCliente());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
				
				result = new Reserva();
				result.setIdReserva(id);
			}
			

		} catch (SQLException e) {
			//System.err.println(e.getMessage());
			result = null;
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return result;
	}

	public int eliminar(int idreserva) throws DAOExcepcion {
		String query = "update reserva set elog = '0' WHERE idreserva=?";
		Connection con = null;
		PreparedStatement stmt = null;
		int i =0;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idreserva);
			i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return i;
	}

	public int actualizar(Reserva vo) throws DAOExcepcion {
		int i = 0;
		String query = "update reserva set fecha=?,descripcion=?,idsala =?,idcliente=? where idreserva=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setDate(1, vo.getFecha());
			stmt.setString(2, vo.getDescripcion());
			stmt.setInt(3, vo.getSala().getIdsala());
			stmt.setInt(4, vo.getCliente().getIdCliente());
			stmt.setInt(5, vo.getIdReserva());
			
			i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return i;
	}

	public Reserva Obtener(int pIdReserva) throws DAOExcepcion {
		
		Reserva _oReserva = null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			
			String query = 	"select  " +
							"idreserva, " +
							"fecha, " +
							"descripcion," +
							"idsala," +
							"b.idcliente," +
							"c.nombres," +
							"c.paterno," +
							"c.materno," +
							"b.correo " +
							"from reserva a inner join cliente b on (a.idcliente = b.idcliente) " +
							"inner join persona c on (b.dni = c.dni) " +
							"where a.idreserva = ? " +
							"and elog = 1;";
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, pIdReserva);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				_oReserva = new Reserva();
				
				Cliente cliente = new Cliente();
				cliente.setIdCliente(rs.getInt("idcliente"));
				cliente.setCorreo(rs.getString("correo"));
				cliente.setNombre(rs.getString("nombres"));
				cliente.setApePaterno(rs.getString("paterno"));
				cliente.setApeMaterno(rs.getString("materno"));
				_oReserva.setCliente(cliente);
				
				_oReserva.setIdReserva(rs.getInt("idreserva"));
				_oReserva.setIdSala(rs.getInt("idsala"));
				_oReserva.setFecha(rs.getDate("fecha"));
				_oReserva.setDescripcion(rs.getString("descripcion"));
				
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return _oReserva;
	}
	
}
